﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.EntityClient;
using System.IO;
using CommonLib;
using System.Linq;

namespace EfDbLayer
{
    public class EfDbProxy : IDbProxy
    {
        void IDbProxy.AddArticleDb(WordStatArticle article)
        {
            using (var db = new DbEntities(GetConnectionString()))
            {
                var author = AddAuthor(article.Author, db);

                if (author == null)
                    return;

                var articleDb = AddArticle(article.Title, article.Date, article.Text, article.Url, article.Source, db);

                if (articleDb == null)
                    return;

                AddAuthorArticle(articleDb, author, db);

                var words = WordParser.PrepareWords(article.Words);

                var dbWords = AddWords(words.Keys, db);

                AddArticleWords(articleDb, dbWords, words, db);

                db.SaveChanges();
            }
        }

        private const string GetWordsQuery = "SELECT Words.WordText, SUM(ArticleWords.WordCount) AS WordCountSum" +
                                             " FROM Words INNER JOIN " +
                                             " ArticleWords ON Words.WordId = ArticleWords.WordId ";

        private const string GetArticleQuery = " INNER JOIN Articles ON Articles.ArticleId = ArticleWords.ArticleId" +
                                               " WHERE (Articles.Source = @source)";

        private string GetEndQuery(bool sortByName)
        {
            var orderBy = sortByName ? "Words.WordText" : "WordCountSum";

            return string.Format(" GROUP BY Words.WordText  ORDER BY {0} OFFSET @toOffset ROWS FETCH NEXT @toTake ROWS ONLY", orderBy);
        }

        Dictionary<string, int> IDbProxy.GetAllWordsData(int page, int itemOnPages, bool sortByName, string source)
        {
            var dic = new Dictionary<string, int>();

            using (var db = new DbEntities(GetConnectionString()))
            {
                var toSkip = page * itemOnPages;

                string queryString = string.Empty;

                bool hasSource = false;

                if (string.IsNullOrWhiteSpace(source))
                    queryString = GetWordsQuery + GetEndQuery(sortByName);
                else
                {
                    queryString = GetWordsQuery + GetArticleQuery + GetEndQuery(sortByName);
                    hasSource = true;
                }

                db.Connection.Open();

                var cmd = CreateCommand(source, itemOnPages, db, queryString, toSkip, hasSource);

                var res = cmd.ExecuteReader();

                while (res.Read())
                {
                    var num = res[1] as int?;
                    int count = 0;
                    if (num.HasValue)
                        count = num.Value;
                    dic.Add(res[0].ToString(), count);
                }
            }

            return dic;
        }

        private DbCommand CreateCommand(string source, int itemOnPages, DbEntities db, string queryString, int toSkip, bool hasSource)
        {
            var cmd = (db.Connection as EntityConnection).StoreConnection.CreateCommand();

            cmd.CommandText = queryString;

            {
                var par = cmd.CreateParameter();
                par.ParameterName = "toOffset";
                par.Value = toSkip;
                cmd.Parameters.Add(par);
            }

            {
                var par = cmd.CreateParameter();
                par.ParameterName = "toTake";
                par.Value = itemOnPages;
                cmd.Parameters.Add(par);
            }

            if (hasSource)
            {
                var par = cmd.CreateParameter();
                par.ParameterName = "source";
                par.Value = source;
                cmd.Parameters.Add(par);
            }

            cmd.CommandType = CommandType.Text;
            return cmd;
        }

        IDictionary<WordStatArticle, int> IDbProxy.GetArticles(string wordText, string currentSource)
        {
            using (var db = new DbEntities(GetConnectionString()))
            {
                var wordId = (from w in db.Words where wordText == w.WordText select w.WordId).FirstOrDefault();

                var articlids = (from wa in db.ArticleWords
                                 where wa.WordId == wordId
                                 select new { wa.ArticleId, wa.WordCount });

                var dic = articlids.ToDictionary(articlid => articlid.ArticleId.Value, articlid => articlid.WordCount.Value);

                var list = dic.Select(d => d.Key);

                var articles = from a in db.Articles where list.Contains(a.ArticleId) select new { a.ArticleTitle, a.ArticleUrl, a.ArticleId, a.ArticleText };

                var res = new Dictionary<WordStatArticle, int>();

                foreach (var article in articles)
                {
                    var a = new WordStatArticle { Title = article.ArticleTitle, Url = article.ArticleUrl, Text = article.ArticleText };

                    res.Add(a, dic[article.ArticleId]);
                }

                return res;
            }
        }

        List<string> IDbProxy.GetAllSoyurces()
        {
            using (var db = new DbEntities(GetConnectionString()))
            {
                return (from a in db.Articles select a.Source).Distinct().ToList();
            }
        }

        IEnumerable<ArticleAuthorData> IDbProxy.GetAllArticles()
        {
            using (var db = new DbEntities(GetConnectionString()))
            {
                return (from article in db.Articles
                        join author in db.Authors on article.ArticleId equals author.AuthorId
                        orderby article.ArticleDate
                        select new { article.ArticleId, article.ArticleTitle, author.NameSurname }).
                        Select(a => new ArticleAuthorData { ArticleId = a.ArticleId, ArticleTitle = a.ArticleTitle, Author = a.NameSurname }).
                        ToList();
            }
        }

        private void AddArticleWords(Articles articleDb, List<Words> dbWords, IDictionary<string, int> words, DbEntities db)
        {
            foreach (var dbWord in dbWords)
            {
                if (!words.ContainsKey(dbWord.WordText))
                    continue;

                var aw = new ArticleWords { Articles = articleDb, Words = dbWord, WordCount = words[dbWord.WordText] };

                db.ArticleWords.AddObject(aw);
            }

            db.SaveChanges();
        }

        private List<Words> AddWords(ICollection<string> words, DbEntities db)
        {
            var res = new List<Words>();
            var wordList = new List<string>(words);

            var list = words.Where(word => db.Words.Any(dbWord => dbWord.WordText == word)).ToList();

            foreach (var wIter in list)
                wordList.Remove(wIter);

            foreach (var dbWord in wordList.Select(word => new Words { WordText = word }))
            {
                res.Add(dbWord);

                db.Words.AddObject(dbWord);
            }

            db.SaveChanges();

            foreach (var v in list)
            {
                string v1 = v;
                var w = db.Words.Where(word => word.WordText == v1).FirstOrDefault();
                if (w != null)
                    res.Add(w);
            }

            return res;

        }

        private void AddAuthorArticle(Articles article, Authors author, DbEntities db)
        {
            var authorArticle = new AuthorArticles { Articles = article, Authors = author };

            db.AuthorArticles.AddObject(authorArticle);

            db.SaveChanges();
        }

        private Articles AddArticle(string title, DateTime date, string text, string url, string source, DbEntities db)
        {
            var article = new Articles
            {
                ArticleTitle = title,
                ArticleText = text,
                ArticleUrl = url,
                ArticleDate = date,
                Source = source
            };

            db.Articles.AddObject(article);

            db.SaveChanges();

            return article;
        }

        private Authors AddAuthor(string authorName, DbEntities db)
        {
            var author = db.Authors.Where(a => a.NameSurname == authorName).FirstOrDefault();

            if (author != null)
                return author;

            var newAuthor = new Authors { NameSurname = authorName };

            db.Authors.AddObject(newAuthor);

            db.SaveChanges();

            return newAuthor;
        }

        private static string GetConnectionString()
        {
            var path = Path.GetFullPath(DirPath);

            var filePath = string.Format(CN2, path);

            var entityBuilder = new EntityConnectionStringBuilder
            {
                Provider = "System.Data.SqlServerCe.4.0",
                ProviderConnectionString = filePath,
                Metadata = @"res://*/EfDbModel.csdl|res://*/EfDbModel.ssdl|res://*/EfDbModel.msl",
            };

            Console.WriteLine(entityBuilder.ToString());

            return entityBuilder.ToString();
        }

        private const string DirPath = @"..\..\test.sdf";
        private const string CN2 = @"Data Source={0}";
    }
}